CREATE TABLE [group] ( 
    id       INTEGER        PRIMARY KEY AUTOINCREMENT,
    name     VARCHAR( 32 )  NOT NULL,
    group_id INTEGER        REFERENCES [group] ( id ) ON DELETE CASCADE
                                                      ON UPDATE CASCADE,
    expand   BOOLEAN        DEFAULT ( 0 ) 
);

CREATE TABLE type ( 
    id           INTEGER         PRIMARY KEY AUTOINCREMENT,
    name         VARCHAR( 32 )   NOT NULL
                                 UNIQUE,
    application  VARCHAR( 256 )  NOT NULL,
    args         VARCHAR( 256 ),
    default_port INTEGER 
);

CREATE TABLE sessions ( 
    id       INTEGER         PRIMARY KEY AUTOINCREMENT,
    name     VARCHAR( 32 )   NOT NULL,
    group_id INTEGER( 16 )   REFERENCES [group] ( id ) ON DELETE CASCADE
                                                       ON UPDATE CASCADE,
    host     VARCHAR( 128 )  NOT NULL,
    info     TEXT 
);

CREATE TABLE sessions2type ( 
    id         INTEGER         PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER         NOT NULL
                               REFERENCES sessions ( id ) ON DELETE CASCADE
                                                          ON UPDATE CASCADE,
    type_id    INTEGER         NOT NULL
                               REFERENCES type ( id ) ON DELETE CASCADE
                                                      ON UPDATE CASCADE,
    port       INTEGER,
    user       VARCHAR( 32 ),
    pass       VARCHAR( 32 ),
    arg1       VARCHAR( 128 ),
    arg2       VARCHAR( 128 ) 
);

INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (2, 'TightVNC', 'C:\Program Files\TightVNC\tvnviewer.exe', '-host=%host% -port=%port% -password=%pass%', 5900);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (3, 'Putty SSH', 'C:\Program Files (x86)\PuTTY\putty.exe', '-ssh -P %port% %user%@%host%', 22);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (4, 'Filezilla SCP', 'C:\Program Files\FileZilla FTP Client\filezilla.exe', 'sftp://%user%@%host%:%port%/', 22);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (5, 'WinSCP', 'C:\Program Files (x86)\WinSCP\WinSCP.exe', 'scp://%user%@%host%:%port%/', 22);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (6, 'RDP', 'E:\utils\mstsc.cmd', '%host% %user% %pass%', 0);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (7, 'HTTP', 'C:\Program Files (x86)\Google\Chrome\Application\chrome.exe', 'http://%host%:%port%/', 80);
INSERT INTO [type] ([id], [name], [application], [args], [default_port]) VALUES (8, 'Putty Telnet', 'C:\Program Files (x86)\PuTTY\putty.exe', '-telnet %host%', 23);
